package dao.Impl;

import dao.ScheduleRepository;
import entity.Schedule;
import utils.JDBCTools;

import java.sql.*;
import java.util.ArrayList;

public class ScheduleRepositoryImpl implements ScheduleRepository {

    /**
     * 从数据库中获取时间表信息表
     */
    @Override
    public  ArrayList<Schedule> getScheduleList(){
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from servicemanagementsystem.schedule";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Schedule schedule = null;
        ArrayList<Schedule> scheduleList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Integer sid = resultSet.getInt(1);
                Integer cid = resultSet.getInt(2);
                Integer eid= resultSet.getInt(3);
                Date start = resultSet.getDate(4);
                Date end = resultSet.getDate(5);
                schedule = new Schedule(sid, cid, eid ,start ,end);
                scheduleList.add(schedule);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection, ps, resultSet);
        }
        return scheduleList;
    }
    /**
     * 添加时间表信息
     */
    @Override
    public boolean addSchedule(Schedule schedule) {
        Connection connection = null;
        String sql = "insert into servicemanagementsystem.schedule (sid, cid, eid, start, end) " +
                "values (?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, schedule.getSid());
            ps.setInt(2, schedule.getCid());
            ps.setInt(3, schedule.getEid());
            ps.setDate(4, schedule.getStart());
            ps.setDate(5, schedule.getEnd());

            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("error when add schedule");
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }

    /**
     * 根据时间表编号查找数据库返回时间表信息对象
     */
    @Override
    public Schedule findById(Integer scheduleId) {
        Connection connection = null;
        String sql = "select * from servicemanagementsystem.schedule where sid=?";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Schedule schedule = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, scheduleId);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Integer sid = resultSet.getInt(1);
                Integer cid = resultSet.getInt(2);
                Integer eid= resultSet.getInt(3);
                Date start = resultSet.getDate(4);
                Date end = resultSet.getDate(5);
                schedule = new Schedule(sid, cid, eid, start, end);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, ps, resultSet);
        }
        return schedule;
    }
    /**
     * 修改时间表信息
     */
    @Override
    public boolean updateSchedule(Schedule schedule) {
        Connection connection = null;
        String sql = "update servicemanagementsystem.schedule set cid=? , eid=?, start=?, end=?" +
                "where sid=?";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, schedule.getCid());
            ps.setInt(2, schedule.getEid());
            ps.setDate(3, schedule.getStart());
            ps.setDate(4, schedule.getEnd());
            ps.setInt(5, schedule.getSid());
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            System.out.println("error when update schedule");
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }

    /**
     * 根据时间表编号删除时间表信息
     */
    @Override
    public boolean deleteSchedule(Integer scheduleId) {
        Connection connection = null;
        String sql = "delete from servicemanagementsystem.schedule where sid = ?";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, scheduleId);
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }

}
